package com.solidtrust.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.solidtrust.entity.Ganji;

public interface GanjiRepository extends JpaRepository<Ganji, Long>{

	Page<Ganji> findByCityAndBrandAndModelAndBuildAndVolumeAndScrapedDateGreaterThanAndMileageBetween(
			Pageable pageable, String city, String brand, String model, String build, Integer volume, Date from, 
			Integer mileageStart, Integer mileageEnd);

	@Query("select max(c.soldPrice), min(c.soldPrice), avg(c.soldPrice), count(c) from Ganji c where c.city = ? and c.brand = ? and c.model = ? and c.build = ? and volume = ? and c.scrapedDate > ? and c.mileage between ? and ?")
	Object[] aggregate(String city, String brand, String model, String build, Integer volume, Date from, 
				Integer mileageStart, Integer mileageEnd);

	@Query("select avg(c.soldPrice) from Ganji c where c.city = ? and c.brand = ? and c.model = ? and c.build = ? and volume = ? and c.scrapedDate > ? and c.mileage between ? and ?")
	Integer averageByCity(String city, String brand, String model, String build, Integer volume, Date from, 
			Integer mileageStart, Integer mileageEnd);
	
	@Query("select avg(c.soldPrice) from Ganji c where c.city in ?1 and c.brand = ?2 and c.model = ?3 and c.build = ?4 and volume = ?5 and c.scrapedDate > ?6 and c.mileage between ?7 and ?8")
	Integer averageByCities(List<String> cities, String brand, String model, String build, Integer volume, Date from, 
			Integer mileageStart, Integer mileageEnd);
	
	@Query("select avg(c.soldPrice) from Ganji c where c.brand = ? and c.model = ? and c.build = ? and volume = ? and c.scrapedDate > ? and c.mileage between ? and ?")
	Integer average(String brand, String model, String build, Integer volume, Date from, 
			Integer mileageStart, Integer mileageEnd);
	
	/* without volume*/
	
	@Query("select avg(c.soldPrice) from Ganji c where c.city = ? and c.brand = ? and c.model = ? and c.build = ? and c.scrapedDate > ? and c.mileage between ? and ?")
	Integer averageByCity1(String city, String brand, String model, String build, Date from, 
			Integer mileageStart, Integer mileageEnd);
	
	@Query("select avg(c.soldPrice) from Ganji c where c.city in ?1 and c.brand = ?2 and c.model = ?3 and c.build = ?4 and c.scrapedDate > ?5 and c.mileage between ?6 and ?7")
	Integer averageByCities1(List<String> cities, String brand, String model, String build, Date from, 
			Integer mileageStart, Integer mileageEnd);
	
	@Query("select avg(c.soldPrice) from Ganji c where c.brand = ? and c.model = ? and c.build = ? and c.scrapedDate > ? and c.mileage between ? and ?")
	Integer average1(String brand, String model, String build, Date from, 
			Integer mileageStart, Integer mileageEnd);

}